package com.firesys.rounting.db;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.firesys.dbhelper.BaseDao;
import com.firesys.dbhelper.DbTableName;
import com.firesys.httpserver.DownloadItem;
import com.firesys.httpserver.RequestUrl;
import com.firesys.httpserver.UpdateVo;
import com.firesys.httpserver.UploadItem;
import com.firesys.httpserver.UploadPOVO;
import com.firesys.povo.DeviceItem;
import com.firesys.povo.DeviceList;
import com.firesys.povo.DeviceSubItem;
import com.firesys.povo.DeviceVo;
import com.firesys.povo.Model;
import com.firesys.povo.SpinnerVo;
import com.firesys.util.DateUtil;
import com.firesys.util.FileNameUtil;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;
import android.widget.Spinner;



/**
 * 设备巡检数据交换类
 * @author King
 *
 */
public class DeviceRoutingDao extends BaseDao {
    
    private SQLiteDatabase  db;
    
    
      
    public SQLiteDatabase getDb() {
        return db;
    }

    public void setDb(SQLiteDatabase db) {
        this.db = db;
    }

    public DeviceRoutingDao(Context context, String SqliteName) {
  	super(context, SqliteName);
  	this.db=super.getDb();
      }
    
    /**
     * 查询当前设备册的巡检或保养数据
     * @param devicelistid  设备册ID
     * @param devicetype   类型（1巡检，2保养）
     * @return  Object 巡检项目信息
     */
    public List<DeviceItem> queryDeviceItem(String devicelistid,String devicetype){
	
	String sql="select * from devicelist where number='"+devicelistid+"'";
	Cursor cursor=db.rawQuery(sql, null);
	String str="";
	List<DeviceItem> list=new ArrayList<DeviceItem>();
	String[] s=null;
	if(cursor.moveToNext()){
	    str=cursor.getString(cursor.getColumnIndex("deviceitem"));
	    if(str.indexOf(",")>0){
		 s=str.split(",");
	    }
	}
	if(cursor!=null){
	    cursor.close();
	}
	sql="select di.[_id] as itemid, di.[name] as itemname,di.[number] as itemnumber,di.[type],di.[forcedpictures]" +
			",dsi.[name] as subname,dsi.[number] as subnumber,dsi.[itemNumber],dsi.[_id] as subid" +
			" from deviceItem di,devicesubitem dsi where di.[number]=dsi.[itemNumber] and di.[type]='"+devicetype+"' " +
			" and di.[number] in (";
	for(int i=0;i<s.length;i++){
	    sql=sql+s[i]+",";
	}	
	sql=sql.substring(0, sql.length()-1);	
	sql=sql+") order by di.number";	
	cursor=db.rawQuery(sql, null);
	  DeviceItem item=null;
	    List<DeviceSubItem> subitem=null;
	while(cursor.moveToNext()){	  
	    String itemid=cursor.getString(cursor.getColumnIndex("itemid"));
	    if(list.size()==0||!list.get(list.size()-1).getDeviceItemID().equals(itemid)){		
		item=new DeviceItem();
		subitem=new ArrayList<DeviceSubItem>();
		item.setDeviceItemID(itemid);
		item.setDeviceItemName(cursor.getString(cursor.getColumnIndex("itemname")));
		item.setDeviceItemNumber(cursor.getString(cursor.getColumnIndex("itemnumber")));
		item.setDeviceItemType(cursor.getString(cursor.getColumnIndex("type")));
		item.setForcedpictures(cursor.getString(cursor.getColumnIndex("forcedpictures")));
		list.add(item);
	    }	   
	   DeviceSubItem suitem=new DeviceSubItem();
	    suitem.setDeviceItemNumbeer(cursor.getString(cursor.getColumnIndex("itemNumber")));
	    suitem.setId(cursor.getString(cursor.getColumnIndex("subid")));	 
	    suitem.setName(cursor.getString(cursor.getColumnIndex("subname")));
	    suitem.setNumber(cursor.getString(cursor.getColumnIndex("subnumber")));
	    
	    subitem.add(suitem);
	    item.setSubitem(subitem);
	}
	if(cursor!=null){
	    cursor.close();
	}
	return list;
	
    }
    
    /**
     * 查询设备信息（分页）
     * @param model
     * @param deviceListid
     * @param devicenumber
     * @param devicename
     * @param copystatus
     * @return
     */
     public List<DeviceVo> queryModelDevice(Model model,String deviceadds,String devicenumber,
	     String devicetype,String copystatus,String devicelistid){
	 
	 StringBuffer sb=new StringBuffer("select * from deviceinfo where   (operationtype='1' or operationtype='0') ");
	 if(deviceadds!=null&&!TextUtils.isEmpty(deviceadds)){
	     sb.append(" and deviceadds like'%").append(deviceadds).append("%'");
	 }
	 if(devicetype!=null&&!TextUtils.isEmpty(devicetype)&&!devicetype.equals("请选择")){
	     sb.append(" and devicetype ='").append(devicetype).append("'");
	 }
	 if(devicenumber!=null&&!TextUtils.isEmpty(devicenumber)){
	     sb.append(" and devicenumber='").append(devicenumber).append("'");
	 }
	 if(copystatus!=null&&!TextUtils.isEmpty(copystatus)&&!copystatus.equals("请选择")){
	   if(copystatus.equals("已巡检")){
	       sb.append(" and iscopystatus='1' ");
	   }else{
	       sb.append(" and iscopystatus='0' ");
	   }
	 }
	 if(devicelistid!=null&&!TextUtils.isEmpty(devicelistid)&&!devicelistid.equals("0")){
	     sb.append("  and devicelistid='").append(devicelistid).append("'");
	 }
	 sb.append("  order by devicelistid,iscopystatus");
	 String sql=sb.toString();
	
	 List<DeviceVo> list=new ArrayList<DeviceVo>();
	 Cursor cursor=super.getAllItems( (model.getIndex() - 1) * model.getView_Count(), model.getView_Count(), sql);
	
	 while(cursor.moveToNext()){
	     DeviceVo device=new DeviceVo();
	     device.setId(cursor.getString(cursor.getColumnIndex("_id")));
	     device.setDeviceId(cursor.getString(cursor.getColumnIndex("deviceid")));
	     device.setDeviceListId(cursor.getString(cursor.getColumnIndex("devicelistid")));
	     device.setDeviceNumber(cursor.getString(cursor.getColumnIndex("devicenumber")));
	     device.setDeviceAdds(cursor.getString(cursor.getColumnIndex("deviceadds")));
	     device.setUseStatus(cursor.getString(cursor.getColumnIndex("useStatus")));
	     device.setOpenStatus(cursor.getString(cursor.getColumnIndex("openStatus")));
	     device.setDeviceCaliber(cursor.getString(cursor.getColumnIndex("devicecaliber")));
	     device.setDeviceType(cursor.getString(cursor.getColumnIndex("devicetype")));
	     device.setDevicePressure(cursor.getString(cursor.getColumnIndex("devicePressure")));
	     device.setInspectionCycle(cursor.getString(cursor.getColumnIndex("inspectionCycle")));
	     device.setInstallationDate(cursor.getString(cursor.getColumnIndex("InstallationDate")));
	     device.setLongitude(cursor.getDouble(cursor.getColumnIndex("longitude")));
	     device.setLatitude(cursor.getDouble(cursor.getColumnIndex("latitude")));
	     device.setMaintenanceCycle(cursor.getString(cursor.getColumnIndex("maintenanceCycle")));
	     device.setProducers(cursor.getString(cursor.getColumnIndex("producers")));
	     device.setProductionDate(cursor.getString(cursor.getColumnIndex("productionDate")));
	     device.setRfidNumber(cursor.getString(cursor.getColumnIndex("RFIDnumber")));
	     device.setCopystatus(cursor.getString(cursor.getColumnIndex("iscopystatus")).equals("1")?"已巡检":"未巡检");
	     list.add(device);	     
	 }
	 if(cursor!=null){
	     cursor.close();
	 }
	return list;
	 
     }
     
     /**
      * 查询总页数
      * @param deviceListid
      * @param devicenumber
      * @param devicename
      * @param copystatus
      * @return
      */
     public int queryPageCount (String deviceadds,String devicenumber,
	     String devicetype,String copystatus,String devicelistid){
	 int page=0;
	 
	 StringBuffer sb=new StringBuffer("select * from deviceinfo   where  (operationtype='1' or operationtype='0') ");
	 if(deviceadds!=null&&!TextUtils.isEmpty(deviceadds)){
	     sb.append(" and deviceadds like'%").append(deviceadds).append("%'");
	 }
	 if(devicetype!=null&&!TextUtils.isEmpty(devicetype)&&!devicetype.equals("请选择")){
	     sb.append(" and devicetype ='").append(devicetype).append("'");
	 }
	 if(devicenumber!=null&&!TextUtils.isEmpty(devicenumber)){
	     sb.append(" and devicenumber='").append(devicenumber).append("'");
	 }
	 if(copystatus!=null&&!TextUtils.isEmpty(copystatus)&&!copystatus.equals("请选择")){
		   if(copystatus.equals("已巡检")){
		       sb.append(" and iscopystatus='1' ");
		   }else{
		       sb.append(" and iscopystatus='0' ");
		   }
		 }
	 if(devicelistid!=null&&!TextUtils.isEmpty(devicelistid)&&!devicelistid.equals("0")){
	     sb.append("  and devicelistid='").append(devicelistid).append("'");
	 }
	 String sql=sb.toString();
	 int k=getCount(sql);
	 page=k % 10 > 0 ? k / 10 + 1 : k / 10;
	return page;
     }
     
     
     /**
      * 查询设备总数
      * @return
      */
     public int queryAllDeviceTotal(String devicelistid){
	 int k=0;
	 String sql="select count(_id) as total from deviceinfo  where (operationtype='1' or operationtype='0')";
	 if(devicelistid!=null){
	     sql=sql+" and devicelistid='"+devicelistid+"'";
	 }
	 Cursor cursor=db.rawQuery(sql, null);
	 if(cursor.moveToNext()){
	     k=cursor.getInt(cursor.getColumnIndex("total"));
	 }
	 if(cursor!=null){
	     cursor.close();
	 }
	 
	return k;
     }
     
     /**
      * 查询所有设备未抄数
      * @return
      */
     public int queryNoCopyDeviceTotal(String devicelistid){
	 int k=0;
	 String sql="select count(_id) as total from deviceinfo  where (operationtype='1' or operationtype='0') and iscopystatus='0'";
	 if(devicelistid!=null){
	     sql=sql+" and devicelistid='"+devicelistid+"'";
	 }
	 Cursor cursor=db.rawQuery(sql, null);
	 if(cursor.moveToNext()){
	     k=cursor.getInt(cursor.getColumnIndex("total"));
	 }
	 if(cursor!=null){
	     cursor.close();
	 }
	return k;
     }
     
     /**
      * 查询未上传数
      * @return
      */
     public int queryNoUploadDeviceTotal(){
	 int k=0;
	 String sql="select count(_id) as total from deviceinfo  where (operationtype='1' or operationtype='0') and iscopystatus='1' and isupload='0'";
	
	 Cursor cursor=db.rawQuery(sql, null);
	 if(cursor.moveToNext()){
	     k=cursor.getInt(cursor.getColumnIndex("total"));
	 }
	 if(cursor!=null){
	     cursor.close();
	 }
	return k;
     }
     
    
     
     /**
      * 通过RFID 编号 或ID查询一个巡检设备信息
      * @param rfidNumber
      * @param deviceid
      * @return
      */
     public DeviceVo queryRfidDevice(String rfidNumber,String deviceid,String  devicelistid){
	 
	 String sql="select * from deviceinfo di,devicelist dl where  di.[devicelistid]=dl.[devicelistid] and (di.[operationtype]='1' or di.[operationtype]='0')  " +
	 		"and (dl.[operationtype]='1' or dl.[operationtype]='0')";
	 if(rfidNumber!=null){
	     sql=sql+" and di.[rfidNumber]='"+rfidNumber+"'";
	 }
	 if(deviceid!=null){
	     sql=sql+" and di.[deviceid]='"+deviceid+"'";
	 }
	 if(devicelistid!=null){
	     sql=sql+" and di.[devicelistid]='"+devicelistid+"'";
	 }
	 System.out.println("------读卡SQL--------"+sql);
	 Cursor cursor=db.rawQuery(sql, null);
	 DeviceVo device=null;
	 if(cursor.moveToNext()){
	     device=new DeviceVo();
	     device.setId(cursor.getString(cursor.getColumnIndex("_id")));
	     device.setDeviceId(cursor.getString(cursor.getColumnIndex("deviceid")));
	     device.setUseStatus(cursor.getString(cursor.getColumnIndex("useStatus")));
	     device.setOpenStatus(cursor.getString(cursor.getColumnIndex("openStatus")));
	     device.setDeviceNumber(cursor.getString(cursor.getColumnIndex("devicenumber")));
	     device.setDeviceAdds(cursor.getString(cursor.getColumnIndex("deviceadds")));
	     device.setDeviceCaliber(cursor.getString(cursor.getColumnIndex("devicecaliber")));
	     device.setDeviceType(cursor.getString(cursor.getColumnIndex("devicetype")));
	     device.setDevicePressure(cursor.getString(cursor.getColumnIndex("devicePressure")));
	     device.setInspectionCycle(cursor.getString(cursor.getColumnIndex("inspectionCycle")));
	     device.setInstallationDate(cursor.getString(cursor.getColumnIndex("InstallationDate")));
	     device.setLongitude(cursor.getDouble(cursor.getColumnIndex("longitude")));
	     device.setLatitude(cursor.getDouble(cursor.getColumnIndex("latitude")));
	     device.setMaintenanceCycle(cursor.getString(cursor.getColumnIndex("maintenanceCycle")));
	     device.setProducers(cursor.getString(cursor.getColumnIndex("producers")));
	     device.setProductionDate(cursor.getString(cursor.getColumnIndex("productionDate")));
	     device.setRfidNumber(cursor.getString(cursor.getColumnIndex("RFIDnumber")));
	     device.setCopystatus(cursor.getString(cursor.getColumnIndex("iscopystatus")));
	     device.setCopydata(cursor.getString(cursor.getColumnIndex("copydata")));
	 
             device.setCopydate(cursor.getString(cursor.getColumnIndex("copydate")));
             device.setDeviceListId(cursor.getString(cursor.getColumnIndex("devicelistid")));
             device.setDeviceListdeviceitem(cursor.getString(cursor.getColumnIndex("deviceitem")));
             device.setRemark(cursor.getString(cursor.getColumnIndex("remark")));
             System.out.println("---------iscopystatus-------"+cursor.getString(cursor.getColumnIndex("iscopystatus")));
             device.setCopystatus(cursor.getString(cursor.getColumnIndex("iscopystatus")).equals("1")?"已巡检":"未巡检");
             device.setOperationtype(cursor.getString(cursor.getColumnIndex("operationtype")));
  
	 }
	 if(cursor!=null){
	     cursor.close();
	 }
	return device;
	 
     }
     
     /**
      * 删除重复设备册
      * @param devicelistid
      * @return
      */
     public boolean delectDuplicateList(String devicelistid){
	    
	        db.delete(DbTableName.DEVICEINFO,"devicelistid='"+devicelistid+"'", null);
	        db.delete(DbTableName.DEVICELIST, "devicelistid='"+devicelistid+"'", null);
	        db.delete(DbTableName.RESOURCES, "devicelistid='"+devicelistid+"'", null);
	        db.delete(DbTableName.NAVIGATION, "waterlistid='"+devicelistid+"'",null);
	 
	return false;
	 
     }
     
     /**
      * 添加绑定信息
      * @param deviceid 
      * @param rfid
      * @param latitude
      * @param longitude
      * @return
      */
     public boolean insertDeviceBanding(String deviceid,String rfid,Double latitude,Double longitude){
	        boolean b=false;
	        
	        ContentValues value=new ContentValues();
	        value.put("RFIDnumber", rfid);
	        value.put("latitude", latitude);
	        value.put("longitude", longitude);
	        value.put("bandingstatus", "0");
	     int k=   db.update(DbTableName.DEVICEINFO, value,"deviceid='"+deviceid+"'",null);
	     if(k>0){
		 b=true;
	     }
	return b;
     }
     
     /**
      * 添加巡检数据
      * @param deviceid
      * @param data
      * @return
      */
     public boolean insertDeviceData(String deviceid,String data,String isrfid,String remark,String devicelistid,String deviceopenstatus){
	 boolean b=false;
	 ContentValues  value=new ContentValues();
	 value.put("copydata", data);
	 value.put("iscopystatus", "1");
	 value.put("isupload", "0");
	 value.put("isnorfid", isrfid);
	 value.put("remark", remark);
	 value.put("deviceopenstatus", deviceopenstatus);
	 value.put("copydate", DateUtil.getNowDate());
	int k= db.update(DbTableName.DEVICEINFO, value, "deviceid='"+deviceid+"' and (operationtype='0' or operationtype='1') and devicelistid='"+devicelistid+"'", null);
	if(k>0){
	    b=true;
	}
	return b;
     }
     
     /**
      * 得到巡检项目信息
      * @param info
      * @param copydata
      * @return
      */
    public List<String>  queryResult(DeviceVo device){
	    List<String> list=new ArrayList<String>();
	    List<DeviceItem> items=queryItems(device.getDeviceListdeviceitem());
	    String[] str=null;
	    list.add("巡检日期:"+((TextUtils.isEmpty(device.getCopydate()))?"无":device.getCopydate()));
	    list.add("备注:"+((TextUtils.isEmpty(device.getRemark()))?"无":device.getRemark()));
	    if(!TextUtils.isEmpty(device.getCopydata())&&device.getCopydata().indexOf(",")>0){
		str=device.getCopydata().split(",");
	    }else if(!TextUtils.isEmpty(device.getCopydata())&&device.getCopydata().length()==1){
		str=new String[]{device.getCopydata()};
	    }
	    
	    for(int i=0;i<items.size();i++){
		if(str==null){
		    list.add("巡检项目: "+items.get(i).getDeviceItemName()+"(未操作)");
		}else{
		    String s=str[i];
		    list.add("巡检项目: "+items.get(i).getDeviceItemName()+"("+items.get(i).getSubitem().get(Integer.parseInt(s)).getName()+")");
		
		}
	    }
	    return list;
	    
    }
     
   
  
    
    /**
     * 查询所有未上的巡检数据
     * @param imei
     * @return
     */
    public List<UploadItem> queryCopyData(String imei,String copyid){
	
	String sql="select * from deviceinfo where  (operationtype='0' or operationtype='1') " +
			"  and isupload='0'  ";
	Cursor cursor=db.rawQuery(sql, null);
	String deviceid="";
	String devicelistid="";
	String operationtype="";
	Cursor c=null;
	List<UploadItem> list=new ArrayList<UploadItem>();
	while(cursor.moveToNext()){
	    UploadItem item=new UploadItem();
	    item.setUrl(RequestUrl.UPLOAD_DATA);
	    Map<String, String> map=new HashMap<String, String>();
	     deviceid=cursor.getString(cursor.getColumnIndex("deviceid"));
	     devicelistid=cursor.getString(cursor.getColumnIndex("devicelistid"));
	    operationtype= cursor.getString(cursor.getColumnIndex("operationtype"));
	     map.put("valveId", deviceid);//阀门ID
	     map.put("dataType", "0");//类型
	      String info=cursor.getString(cursor.getColumnIndex("deviceitem"));
	      String data=cursor.getString(cursor.getColumnIndex("copydata"));
	     
	      map.put("dataInspect", uproutingData(info, data));
	      map.put("doDate", cursor.getString(cursor.getColumnIndex("copydate")));
	      map.put("cpId", copyid);
	      map.put("mark", cursor.getString(cursor.getColumnIndex("remark")));
	     map.put("joblist", devicelistid);
	    List<UpdateVo> update=new ArrayList<UpdateVo>();	    
	    UpdateVo upda=new UpdateVo();
	   upda.setTablename(DbTableName.DEVICEINFO);   
	   ContentValues value=new ContentValues();
	   value.put("isupload", "1");
	   upda.setValues(value);
	   upda.setWhereClause("deviceid='"+deviceid+"' and devicelistid='"+devicelistid+"' and operationtype='"+operationtype+"'");
	   update.add(upda);
	    item.setUp(update);
	    item.setMap(map);		
	    String sql1="select * from resources where otherid='"+deviceid+"' and status='"+1+
		    "' and devicelistid ='"+devicelistid+"'";
		    c=db.rawQuery(sql1, null);
		    List<UploadPOVO> imglist=new ArrayList<UploadPOVO>();
		    while(c.moveToNext()){
			File f=new File(c.getString(c.getColumnIndex("path")));
			System.out.println("-----fileName--------"+f.getName());
			if(f.exists()){
			    UploadPOVO up=new UploadPOVO();
			    up.setFilevalue(f);
			    up.setFileVariable("image");
			
			    imglist.add(up);
			}
		    }
		    if(imglist.size()>0){
			item.setUpfile(imglist);
		    }
	    
	 
	   list.add(item);
	   UploadItem item1=new UploadItem();
	    item1.setUrl(RequestUrl.UPLOAD_DEVICE_OPENSTATUS);
	    Map<String, String> map1=new HashMap<String, String>();
	     map1.put("valveId", deviceid);
	     map1.put("cpId", copyid);
	     map1.put("duTime", cursor.getString(cursor.getColumnIndex("copydate")));
	     map1.put("status", cursor.getString(cursor.getColumnIndex("deviceopenstatus")));
	     item1.setMap(map1);
	     list.add(item1);
	}
	System.out.println("------抄表数据长度-------"+list.size());
        if(c!=null){
            c.close();
        }
        if(cursor!=null){
            cursor.close();
        }
	return list;
	   
    }
    /**
     *查询所有表册
     * @return
     */
    public List<SpinnerVo>  queryAllDeviceList(){
	List<SpinnerVo> list=new ArrayList<SpinnerVo>();
	SpinnerVo s=new SpinnerVo("0", "请选择");
	list.add(s);
	String sql="select * from devicelist where (operationtype='0' or operationtype='1') order by operationtype";
	Cursor cursor=db.rawQuery(sql, null);
	while(cursor.moveToNext()){
	    String value=cursor.getString(cursor.getColumnIndex("name"));
	    if(cursor.getString(cursor.getColumnIndex("operationtype")).equals("0")){
		value=value+"(周期巡检)";
	    }else{
		value=value+"(临时巡检)";
	    }
	    SpinnerVo  spinner=new SpinnerVo(cursor.getString(cursor.getColumnIndex("devicelistid")), value);
	    list.add(spinner);
	}
	if(cursor!=null){
	    cursor.close();
	}
	return list;
    }
    
    /**
     * 查询未上传的绑定数
     * @return
     */
    public int queryNoUploadBanding(){
	  int k=0;
	  String sql="select count(_id) as total  from deviceinfo where (operationtype='0' or operationtype='1') and bandingstatus='0' ";
	  Cursor cursor=db.rawQuery(sql, null);
	  if(cursor.moveToNext()){
	      k=cursor.getInt(cursor.getColumnIndex("total"));
	  }
	  if(cursor!=null){
	      cursor.close();
	  }
	  System.out.println("-------------total---------"+k);
	return k;
    }
    
    /**
     * 查询所有应该上传的绑定数
     * @return
     */
    public int queryUploadBnding(){
	  int k=0;
	  String sql="select count(_id) as total  from deviceinfo where (operationtype='0' or operationtype='1') and " +
	  		"(bandingstatus='0' or bandingstatus='1')  ";
	  Cursor cursor=db.rawQuery(sql, null);
	  if(cursor.moveToNext()){
	      k=cursor.getInt(cursor.getColumnIndex("total"));
	  }
	  if(cursor!=null){
	      cursor.close();
	  }
	return k;
    }
    
    /**
     * 查询所有未上传的图文数
     * @return
     */
    public int queryNoUploadTw(){
	  int k=0;
	  String sql="select count(_id) as total  from deviceinfo where (operationtype='0' or operationtype='1') and Navigationupload='0' ";
	  Cursor cursor=db.rawQuery(sql, null);
	  if(cursor.moveToNext()){
	      k=cursor.getInt(cursor.getColumnIndex("total"));
	  }
	  if(cursor!=null){
	      cursor.close();
	  }
	return k;
    }
    
    /**
     * 查询所有应上传的图文数
     * @return
     */
    public int queryUploadTw(){
	  int k=0;
	  String sql="select count(_id) as total  from deviceinfo where (operationtype='0' or operationtype='1') and " +
	  		" ( Navigationupload='0' or Navigationupload='1') ";
	  Cursor cursor=db.rawQuery(sql, null);
	  
	  while(cursor.moveToNext()){
	      k=cursor.getInt(cursor.getColumnIndex("total"));
	      
	  }
	  if(cursor!=null){
	      cursor.close();
	  }
	return k;
    }
    
    /**
     * 查询未上传的图文
     * @return
     */
    public int queryUploadTwen(){

	  int k=0;
	  String sql="select count(_id) as total  from deviceinfo where (operationtype='0' or operationtype='1') and " +
	  		"  Navigationupload='0' ";
	  Cursor cursor=db.rawQuery(sql, null);
	  
	  while(cursor.moveToNext()){
	      k=cursor.getInt(cursor.getColumnIndex("total"));
	      
	  }
	  if(cursor!=null){
	      cursor.close();
	  }
	return k;
  
    }
    
    /**
     * 查询所有巡检数
     * @return
     */
    public int queryCopyData(){
	  int k=0;
	  String sql="select count(_id) as total  from deviceinfo where (operationtype='0' or operationtype='1') and " +
	  		" iscopystatus='1'";
	  Cursor cursor=db.rawQuery(sql, null);
	  if(cursor.moveToNext()){
	      k=cursor.getInt(cursor.getColumnIndex("total"));
	  }
	  if(cursor!=null){
	      cursor.close();
	  }
	return k;
    }
    
    /**
     * 查询所有 下载的图文图片
     * @return
     */
    public List<DownloadItem>  queryNoDown(){
	FileNameUtil f=new FileNameUtil();
	String sql="select * from graphicnavigation where (type='0' or type='1') and isimg='1' " +
			" and isdownload='0' ";

	Cursor c=db.rawQuery(sql, null);
	System.out.println(c.getCount()+"---------------------");
	List<DownloadItem> list=new ArrayList<DownloadItem>();
	String webdesc="";
	String othernumber="";
	String filename="";
	String s[]=null;
	String path="";
	while(c.moveToNext()){
	      webdesc=c.getString(c.getColumnIndex("imgwebname"));
	      System.out.println("------webdesc-------"+webdesc);
	      othernumber=c.getString(c.getColumnIndex("othernumber"));
	      s=webdesc.split("/");
	      filename=s[s.length-1];
	      path=f.getSDPath()+"/DeviceFile/DaoHang/"+othernumber;
	      f.SDCardPath("DaoHang");
	   
	      File file=new File(path+"/"+filename);
	      if(!file.exists()){
		  UpdateVo uv=new UpdateVo();
		  ContentValues values=new ContentValues();
		  values.put("isdownload", "1");
		  values.put("imgpath", path+"/"+filename);
		  values.put("isimg", "1");
		  uv.setTablename(DbTableName.NAVIGATION);
		  uv.setValues(values);
		  uv.setWhereClause("_id='"+c.getString(c.getColumnIndex("_id"))+"'");
		
		  DownloadItem item=new DownloadItem(filename, webdesc, path,uv);
		  list.add(item);
	      }else{
		  ContentValues values=new ContentValues();
		  values.put("imgpath", path+"/"+filename);
		  
		  values.put("isimg", "1");
		  values.put("isdownload", "1");
		  db.update(DbTableName.NAVIGATION, values, "_id='"+c.getString(c.getColumnIndex("_id"))+"'", null);
	      }
	}
	
	if(c!=null){
	    c.close();
	}
	return list;
	
    }
    
    
    /**
     * 查询设备册信息
     * @return
     */
    public List<DeviceList>  queryAllDeviceList(String deviceid){
    	List<DeviceList>  list=new ArrayList<DeviceList>();
    	String sql="select * from devicelist";
    	if(deviceid!=null){
    		sql=sql+" where devicelistid='"+deviceid+"'";
    	}
    	Cursor  cursor=db.rawQuery(sql, null);
    	Cursor  cursor1=null;
    	while(cursor.moveToNext()){
    		DeviceList  device=new DeviceList();
    		device.setDeviceListId(cursor.getString(cursor.getColumnIndex("devicelistid")));
    		device.setDeviceListName(cursor.getString(cursor.getColumnIndex("name")));
    		device.setEndDate(cursor.getString(cursor.getColumnIndex("enddate")));
    		device.setStartDate(cursor.getString(cursor.getColumnIndex("startdate")));
    		device.setTotal(cursor.getInt(cursor.getColumnIndex("total")));
    		String sql1="select count(_id) as nocopy from deviceinfo where devicelistid='"+device.getDeviceListId()+"' and iscopystatus='1'";
    		cursor1=db.rawQuery(sql1, null);
    		if(cursor1.moveToLast()){
    			device.setNocopy(cursor1.getInt(cursor1.getColumnIndex("nocopy")));
    		}else{
    			device.setNocopy(0);
    		}
    		list.add(device);
    	}
    	if(cursor!=null){
    		cursor.close();
    	}
    	if(cursor1!=null){
    		cursor1.close();
    	}
		return list;
    }
    
   
}
